package com.library.dao.db;

import com.library.dao.BookRepository;
import com.library.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

@Repository
public class JdbcBookRepository implements BookRepository {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    // 构造函数，注入 JdbcTemplate
    public JdbcBookRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    // RowMapper 用于将数据库中的一行记录映射成 Book 对象
    private RowMapper<Book> bookRowMapper = (rs, rowNum) -> {
        Book book = new Book();
        book.setId(rs.getLong("id"));  // 设置书籍 ID
        book.setIsbn(rs.getString("isbn"));  // 设置 ISBN
        book.setTitle(rs.getString("title"));  // 设置书名
        book.setAuthor(rs.getString("author"));  // 设置作者
        book.setPublisher(rs.getString("publisher"));  // 设置出版社
        book.setCategory(rs.getString("category"));  // 设置类别
        book.setTotalCopies(rs.getInt("totalCopies"));  // 设置总副本数
        book.setAvailableCopies(rs.getInt("availableCopies"));  // 设置可借副本数
        return book;  // 返回映射后的 Book 对象
    };

    @Override
    public List<Book> findByTitle(String query) {
        // 使用 LIKE 进行模糊查询，% 表示匹配任意字符
        String sql = "SELECT * FROM books WHERE title LIKE ?";
        // 在关键字前后添加 "%" 以支持模糊匹配
        String likeQuery = "%" + query + "%";

        // 使用 query 查询所有匹配的记录并返回 List<book>
        return jdbcTemplate.query(sql, new Object[]{likeQuery}, bookRowMapper);
    }


    // 获取所有图书
    @Override
    public List<Book> findAll() {
        String sql = "SELECT * FROM books";

        // 使用 query 查询所有记录并返回 List<book> 类型的结果
        List<Book> books = jdbcTemplate.query(sql, bookRowMapper);

        return books;  // 返回所有查询到的图书
    }

    // 根据图书 ID 查找图书
    @Override
    public Book findById(Long id) {
        String sql = "SELECT * FROM books WHERE id = ?";
        return jdbcTemplate.queryForObject(sql, new Object[]{id}, bookRowMapper);  // 查询并返回单个图书
    }

    // 根据作者查找图书
    @Override
    public List<Book> findByAuthor(String author) {
        String sql = "SELECT * FROM books WHERE author = ?";
        return jdbcTemplate.query(sql, new Object[]{author}, bookRowMapper);  // 查询并返回结果
    }

    // 根据 ISBN 查找图书
    @Override
    public Optional<Book> findByIsbn(String isbn) {
        String sql = "SELECT * FROM books WHERE isbn = ?";
        try {
            // 查询并返回单个图书对象
            Book book = jdbcTemplate.queryForObject(sql, new Object[]{isbn}, bookRowMapper);
            return Optional.ofNullable(book);  // 如果查询结果为空，返回 Optional.empty()
        } catch (Exception e) {
            return Optional.empty();  // 捕获异常并返回空的 Optional
        }
    }

    // 根据类别查找图书
    @Override
    public List<Book> findByCategory(String category) {
        String sql = "SELECT * FROM books WHERE category = ?";
        return jdbcTemplate.query(sql, new Object[]{category}, bookRowMapper);  // 查询并返回结果
    }
    // 查找可借阅副本数大于指定数量的图书
    @Override
    public List<Book> findByAvailableCopiesGreaterThan(int availableCopies) {
        String sql = "SELECT * FROM books WHERE availableCopies > ?";
        return jdbcTemplate.query(sql, new Object[]{availableCopies}, bookRowMapper);  // 查询并返回结果
    }

    // 根据 ID 删除图书
    @Override
    public void deleteById(Long id) {
        String sql = "DELETE FROM books WHERE id = ?";
        jdbcTemplate.update(sql, id);  // 执行删除操作
    }

    @Override
    public void save(Book book) {
        // If the book already exists, we update it
        if (book.getId() != null) {
            String sql = "UPDATE books SET isbn = ?, title = ?, author = ?, publisher = ?, category = ?, totalCopies = ?, availableCopies = ? WHERE id = ?";
            jdbcTemplate.update(sql,
                    book.getIsbn(),
                    book.getTitle(),
                    book.getAuthor(),
                    book.getPublisher(),
                    book.getCategory(),
                    book.getTotalCopies(),
                    book.getAvailableCopies(),
                    book.getId()
            );
        } else {
            // If the book does not exist, we insert a new one
            String sql = "INSERT INTO books (isbn, title, author, publisher, category, totalCopies, availableCopies) VALUES (?, ?, ?, ?, ?, ?, ?)";
            jdbcTemplate.update(sql,
                    book.getIsbn(),
                    book.getTitle(),
                    book.getAuthor(),
                    book.getPublisher(),
                    book.getCategory(),
                    book.getTotalCopies(),
                    book.getAvailableCopies()
            );
        }
    }


    /**
     * 根据书名和作者查找图书
     */
    @Override
    public List<Book> findByTitleAndAuthor(String title, String author) {
        String sql = "SELECT * FROM books WHERE title = ? AND author = ?";
        return jdbcTemplate.query(sql, new Object[]{title, author}, bookRowMapper);  // 查询并返回结果
    }
}
